﻿#region "Copyright (C) Lenny Granovsky. 2011-2013"
//    This library provides extended cache capabilities to the ASP.NET applications.
//
//                Copyright (C) Lenny Granovsky. 2011-2013. 
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 2 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.
//
//    This program comes with ABSOLUTELY NO WARRANTY.
//    This is free software, and you are welcome to redistribute it
//    under certain conditions;
#endregion

using System;
using System.Diagnostics;
using System.Configuration;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Security.Cryptography;
using System.Text;
using System.Linq;
using System.Web.Caching;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using FlexiCache.Utilities;
using System.Text.RegularExpressions;

namespace FlexiCache
{
    /// <summary>
    /// Class provides functionality for out-of-process Cache based on Sql DB. 
    /// It implements the OutputCacheProvider class. 
    /// </summary>
    /// <remarks>This class is not intended to be used directly.</remarks>
    public class SqlDBCacheStorageAdapter : CacheDataUtility, IDisposable, IExternalCacheStorageAdapter
    {
        private const int MaxShortDataLength = 7000;
        private static Regex metadataRegex;

        static SqlDBCacheStorageAdapter()
        {
            metadataRegex = new Regex(@"([\!\%\[\]\x5F\^])", RegexOptions.Compiled | RegexOptions.CultureInvariant);
        }

        /// <summary>
        /// Default constructor.
        /// </summary>
        public SqlDBCacheStorageAdapter()
            : base()
        {
        }

        public void ConnectionTest()
        {
            SqlConnection conn = new SqlConnection(this.DBConnectionString);
            conn.Open();
            conn.Close();
            conn.Dispose();
        }

        private SqlCommand CreateSqlCommand(string storedProcedureName)
        {
            SqlConnection conn = new SqlConnection(this.DBConnectionString);
            SqlCommand oCmd = new SqlCommand(storedProcedureName, conn);
            oCmd.CommandType = CommandType.StoredProcedure;
            return oCmd;
        }

        private void DisposeSqlCommand(ref SqlCommand command)
        {
            if (command != null)
            {
                command.Dispose();
                if (command.Connection != null)
                {
                    if (command.Connection.State != ConnectionState.Closed)
                        command.Connection.Close();
                }
            }
        }

        /// <summary>
        /// Method gets a data from the cache using specified key.
        /// </summary>
        /// <param name="key">Data key.</param>
        /// <returns>Returns cached object.</returns>
        public object Get(string key)
        {
            SqlCommand oCmd = null;
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            object entry = null;
            try
            {
                string storedKey = ComputeCacheStorageKey(key);
                oCmd = this.CreateSqlCommand("usp_GetCacheItem");
                oCmd.Parameters.AddWithValue("@Id", storedKey);
                oCmd.Parameters.AddWithValue("@UtcExpiresStamp", DateTime.UtcNow);
                oCmd.Parameters.Add("@Expires", SqlDbType.DateTime);
                oCmd.Parameters["@Expires"].Direction = ParameterDirection.Output;
                oCmd.Parameters.AddWithValue("@DataLength", decimal.Zero);
                oCmd.Parameters["@DataLength"].Direction = ParameterDirection.Output;
                oCmd.Parameters.Add("@ItemShort", SqlDbType.VarBinary, MaxShortDataLength);
                oCmd.Parameters["@ItemShort"].Direction = ParameterDirection.Output;
                oCmd.Parameters.AddWithValue("@SessionId", "");
                oCmd.Parameters["@SessionId"].Direction = ParameterDirection.Output;
                oCmd.Connection.Open();
                object data = oCmd.ExecuteScalar();
                int length = 0;
                if (oCmd.Parameters["@DataLength"].Value != null && !Convert.IsDBNull(oCmd.Parameters["@DataLength"].Value))
                    length = Convert.ToInt32(oCmd.Parameters["@DataLength"].Value);
                if (data != null)
                {
#if DEBUG
                    if (length > 0 && (data as byte[]).Length != length)
                        throw new Exception(string.Format("Data length long is not correct: was {1} instead of {0}.", (data as byte[]).Length.ToString(), length.ToString()));
#endif
                    entry = Deserialize((byte[])data);
#if TRACE
                    timer.Stop();
                    Trace.WriteLine(string.Format("{0}.Get({1}) result: Cache Item Long deserialized and ready. Time: {2} ms.", this.TracePrefix, key, timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
                }
                else if (oCmd.Parameters["@ItemShort"].Value != null && !Convert.IsDBNull(oCmd.Parameters["@ItemShort"].Value))
                {
                    data = oCmd.Parameters["@ItemShort"].Value;
#if DEBUG
                    if (length > 0 && (data as byte[]).Length != length)
                        throw new Exception(string.Format("Data length short is not correct: was {1} instead of {0}.", (data as byte[]).Length.ToString(), length.ToString()));
#endif
                    entry = Deserialize((byte[])data);
#if TRACE
                    timer.Stop();
                    Trace.WriteLine(string.Format("{0}.Get({1}) result: Cache Item Short deserialized and ready. Time: {2} ms.", this.TracePrefix, key, timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
                }
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.Get({1}) result: Exception: {2}.", this.TracePrefix, key, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }

            return entry;
        }

        /// <summary>
        /// Method determines whether a cache entry for specified key exists in the cache.
        /// </summary>
        /// <param name="key">Data key.</param>
        /// <returns>Returns true if the cache contains a cache entry whose key matches key; otherwise, false.</returns>
        public bool Contains(string key)
        {
            SqlCommand oCmd = null;
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            try
            {
                string storedKey = ComputeCacheStorageKey(key);
                oCmd = this.CreateSqlCommand("usp_ContainsCacheItem");
                oCmd.Parameters.AddWithValue("@Id", storedKey);
                oCmd.Parameters.AddWithValue("@UtcExpiresStamp", DateTime.UtcNow);
                oCmd.Parameters.Add("@HasRecord", SqlDbType.Int);
                oCmd.Parameters["@HasRecord"].Direction = ParameterDirection.Output;
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
                bool contains = false;
                if (oCmd.Parameters["@HasRecord"].Value != null && !Convert.IsDBNull(oCmd.Parameters["@HasRecord"].Value))
                    contains = Convert.ToInt32(oCmd.Parameters["@HasRecord"].Value) > 0;
#if TRACE
                timer.Stop();
                Trace.WriteLine(string.Format("{0}.Contains({1}) result: Cache Item {3}. Time: {2} ms.", this.TracePrefix, key, timer.Elapsed.TotalMilliseconds.ToString("F2"), contains ? "exists" : "does not exist"));
#endif

                return contains;
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.Contains({1}) result: Exception: {2}.", this.TracePrefix, key, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
#if !DEBUG
            return false;
#endif
        }

        /// <summary>
        /// Method adds data into the cache with specified key and expiration.
        /// </summary>
        /// <param name="key">Data key.</param>
        /// <param name="entry">Data to cache.</param>
        /// <param name="utcExpiry">Expiration time (UTC).</param>
        /// <param name="sessionId">The Session identifier, optional.</param>
        /// <returns>Returns cached object.</returns>
        public object Add(string key, object entry, DateTime utcExpiry, string sessionId)
        {
            return Add(key, entry, utcExpiry, sessionId, null);
        }

        /// <summary>
        /// Method adds data into the cache with specified key and expiration.
        /// </summary>
        /// <param name="key">Data key.</param>
        /// <param name="entry">Data to cache.</param>
        /// <param name="utcExpiry">Expiration time (UTC).</param>
        /// <param name="sessionId">The Session identifier, optional.</param>
        /// <param name="metaTags">The meta tags associated with the cached data, optional.</param>
        /// <returns>Returns cached object.</returns>
        public object Add(string key, object entry, DateTime utcExpiry, string sessionId, string[] metaTags)
        {
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            if (entry == null) //remove data if null is to be added
            {
                this.Remove(key);
                return entry;
            }
            string storedKey = ComputeCacheStorageKey(key);

            if (utcExpiry == DateTime.MaxValue)
                utcExpiry = DateTime.UtcNow.AddMinutes(5);

            SqlCommand oCmd = null;
            try
            {
                oCmd = this.CreateSqlCommand("usp_GetCacheItem");
                oCmd.Parameters.AddWithValue("@Id", storedKey);
                oCmd.Parameters.AddWithValue("@UtcExpiresStamp", DateTime.UtcNow);
                oCmd.Parameters.Add("@Expires", SqlDbType.DateTime);
                oCmd.Parameters["@Expires"].Direction = ParameterDirection.Output;
                oCmd.Parameters.AddWithValue("@DataLength", decimal.Zero);
                oCmd.Parameters["@DataLength"].Direction = ParameterDirection.Output;
                oCmd.Parameters.Add("@ItemShort", SqlDbType.VarBinary, MaxShortDataLength);
                oCmd.Parameters["@ItemShort"].Direction = ParameterDirection.Output;
                oCmd.Parameters.AddWithValue("@SessionId", "");
                oCmd.Parameters["@SessionId"].Direction = ParameterDirection.Output;
                oCmd.Connection.Open();
                object data = oCmd.ExecuteScalar();
                if (data != null)
                {
                    object tmp = Deserialize((byte[])data);
#if TRACE
                    timer.Stop();
                    Trace.WriteLine(string.Format("{0}.Add({1}, {2}, {3}). (Long Existed) Time: {4} ms.", this.TracePrefix, key, entry, utcExpiry, timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
                    return tmp;
                }
                else if (oCmd.Parameters["@ItemShort"].Value != null && !Convert.IsDBNull(oCmd.Parameters["@ItemShort"].Value))
                {
                    object tmp = Deserialize((byte[])oCmd.Parameters["@ItemShort"].Value);
#if TRACE
                    timer.Stop();
                    Trace.WriteLine(string.Format("{0}.Add({1}, {2}, {3}). (Short Existed) Time: {4} ms.", this.TracePrefix, key, entry, utcExpiry, timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
                    return tmp;
                }

                //add item
                byte[] dataIn = Serialize(entry);
                oCmd.Parameters.Clear();
                oCmd.Parameters.AddWithValue("@Id", storedKey);
                oCmd.Parameters.AddWithValue("@CreatedDT", DateTime.UtcNow);
                oCmd.Parameters.AddWithValue("@Expires", utcExpiry);
                oCmd.Parameters.AddWithValue("@DataLength", dataIn.Length);
                if (dataIn.Length > MaxShortDataLength)
                {
                    oCmd.CommandText = "usp_SetCacheItemLong";
                    oCmd.Parameters.AddWithValue("@ItemLong", dataIn);
                }
                else
                {
                    oCmd.CommandText = "usp_SetCacheItemShort";
                    oCmd.Parameters.AddWithValue("@ItemShort", dataIn);
                }
                oCmd.Parameters.AddWithValue("@SessionId", sessionId);
                oCmd.Parameters.AddWithValue("@Metadata", base.BuildMetadata(metaTags));
                oCmd.ExecuteNonQuery();
#if TRACE
                timer.Stop();
                Trace.WriteLine(string.Format("{0}.Add({1}, {2}, {3}). Length: {4} bytes. (Inserted) Time: {5} ms.", this.TracePrefix, key, entry, utcExpiry, dataIn.Length.ToString(), timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.Add({1}, {2}, {3}). Exception: {4}.", this.TracePrefix, key, entry, utcExpiry, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }

            return entry;
        }

        /// <summary>
        /// Method sets (updates or adds) data into the cache with specified key and expiration.
        /// </summary>
        /// <param name="key">Data key.</param>
        /// <param name="entry">Data to cache.</param>
        /// <param name="utcExpiry">Expiration time (UTC).</param>
        /// <param name="sessionId">The Session identifier, optional.</param>
        /// <returns>Returns boolean value indicating success.</returns>
        public bool TrySet(string key, object entry, DateTime utcExpiry, string sessionId)
        {
            return TrySet(key, entry, utcExpiry, sessionId, null);
        }

        /// <summary>
        /// Method sets (updates or adds) data into the cache with specified key and expiration.
        /// </summary>
        /// <param name="key">Data key.</param>
        /// <param name="entry">Data to cache.</param>
        /// <param name="utcExpiry">Expiration time (UTC).</param>
        /// <param name="sessionId">The Session identifier, optional.</param>
        /// <param name="metaTags">The meta tags associated with the cached data, optional.</param>
        /// <returns>Returns boolean value indicating success.</returns>
        public bool TrySet(string key, object entry, DateTime utcExpiry, string sessionId, string[] metaTags)
        {
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            if (entry == null) //remove data if null is to be set
            {
                this.Remove(key);
                return true;
            }

            SqlCommand oCmd = null;
            try
            {
                string storedKey = ComputeCacheStorageKey(key);
                byte[] data = Serialize(entry);

                oCmd = this.CreateSqlCommand("");
                oCmd.Parameters.AddWithValue("@Id", storedKey);
                oCmd.Parameters.AddWithValue("@CreatedDT", DateTime.UtcNow);
                oCmd.Parameters.AddWithValue("@Expires", utcExpiry);
                oCmd.Parameters.AddWithValue("@DataLength", data.Length);
                if (data.Length > MaxShortDataLength)
                {
                    oCmd.CommandText = "usp_SetCacheItemLong";
                    oCmd.Parameters.AddWithValue("@ItemLong", data);
                }
                else
                {
                    oCmd.CommandText = "usp_SetCacheItemShort";
                    oCmd.Parameters.AddWithValue("@ItemShort", data);
                }
                oCmd.Parameters.AddWithValue("@SessionId", sessionId);
                oCmd.Parameters.AddWithValue("@Metadata", base.BuildMetadata(metaTags));
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
#if TRACE
                timer.Stop();
                Trace.WriteLine(string.Format("{0}.TrySet({1}, {2}, {3}). Length: {4} bytes. Time: {5} ms.", this.TracePrefix, key, entry, utcExpiry, data.LongLength.ToString(), timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.TrySet({1}, {2}, {3}). Exception: {4}.", this.TracePrefix, key, entry, utcExpiry, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
            return true;
        }

        /// <summary>
        /// Method removes data from the cache using specified key.
        /// </summary>
        /// <param name="key">Data key.</param>
        public void Remove(string key)
        {
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            SqlCommand oCmd = null;
            try
            {
                string storedKey = ComputeCacheStorageKey(key);

                oCmd = this.CreateSqlCommand("usp_RemoveCacheItem");
                oCmd.Parameters.AddWithValue("@Id", storedKey);
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
#if TRACE
                timer.Stop();
                Trace.WriteLine(string.Format("{0}.Remove({1}). Time: {2} ms.", this.TracePrefix, key, timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.Remove({1}). Exception: {2}.", this.TracePrefix, key, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
        }

        /// <summary>
        /// Method removes data from the cache using specified meta tags.
        /// </summary>
        /// <remarks>The order of meta tags must be the same as it was when data was saved. For the ExternalCacheProvider the path=url comes first, then all other tags in alphabetical order.</remarks>
        /// <param name="metaTags">An array of meta tags.</param>
        public void Remove(string[] metaTags)
        {
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            SqlCommand oCmd = null;
            try
            {
                if (metaTags == null)
                    return;
                if (metaTags.Length == 0)
                    return;
                string metadata = metadataRegex.Replace(string.Join("][", metaTags), @"!$1");
                metadata = string.Format("%![{0}!]%", metadata.Replace("!]![", "!]%!["));

                oCmd = this.CreateSqlCommand("usp_RemoveItemsByMetadata");
                oCmd.Parameters.AddWithValue("@MetadataPattern", metadata);
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
#if TRACE
                timer.Stop();
                Trace.WriteLine(string.Format("{0}.Remove[by meta tags]({1}). Time: {2} ms.", this.TracePrefix, base.BuildMetadata(metaTags), timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.Remove[by meta tags]({1}). Exception: {2}.", this.TracePrefix, base.BuildMetadata(metaTags), ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
        }

        /// <summary>
        /// Method gets the total count of records in the database instance.
        /// </summary>
        /// <returns>Returns the total count of records in the database instance.</returns>
        public long GetCount()
        {
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            SqlCommand oCmd = null;
            long cnt = 0;
            try
            {
                oCmd = this.CreateSqlCommand("usp_GetCacheItemsCount");
                oCmd.Parameters.AddWithValue("@ItemsCount", decimal.Zero);
                oCmd.Parameters["@ItemsCount"].Direction = ParameterDirection.Output;
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
                if (oCmd.Parameters["@ItemsCount"].Value != null && !Convert.IsDBNull(oCmd.Parameters["@ItemsCount"].Value))
                    cnt = Convert.ToInt64(oCmd.Parameters["@ItemsCount"].Value);
#if TRACE
                timer.Stop();
                Trace.WriteLine(string.Format("{0}.GetCount(). Time: {1} ms.", this.TracePrefix, timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.GetCount(). Exception: {1}.", this.TracePrefix, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
            return cnt;
        }

        /// <summary>
        /// Method removes all records from the database instance.
        /// </summary>
        public void RemoveAll()
        {
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            SqlCommand oCmd = null;
            try
            {
                oCmd = this.CreateSqlCommand("usp_RemoveAllItems");
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
#if TRACE
                timer.Stop();
                Trace.WriteLine(string.Format("{0}.RemoveAll(). Time: {1} ms.", this.TracePrefix, timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.RemoveAll(). Exception: {1}.", this.TracePrefix, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
        }

        /// <summary>
        /// Method removes all expired records from the database instance.
        /// </summary>
        public void RemoveExpired()
        {
#if TRACE
            Stopwatch timer = new Stopwatch();
            timer.Start();
#endif
            SqlCommand oCmd = null;
            try
            {
                oCmd = this.CreateSqlCommand("usp_RemoveExpiredItems");
                oCmd.Parameters.AddWithValue("@UtcExpiresStamp", DateTime.UtcNow);
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
#if TRACE
                timer.Stop();
                Trace.WriteLine(string.Format("{0}.RemoveExpired(). Time: {1} ms.", this.TracePrefix, timer.Elapsed.TotalMilliseconds.ToString("F2")));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                timer.Stop();
                Trace.TraceError(string.Format("{0}.RemoveExpired(). Exception: {1}.", this.TracePrefix, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
        }

        /// <summary>
        /// Method compacts data in the database.
        /// </summary>
        public void Compact()
        {
            //ignore for SQL Server
        }

        /// <summary>
        /// Method updates the expiration date and time for specified item to renew it for next session timeout interval.
        /// </summary>
        public void Renew(string sessionId, DateTime utcExpiry)
        {
            SqlCommand oCmd = null;
            try
            {
                oCmd = this.CreateSqlCommand("usp_RenewSessionItems");
                oCmd.Parameters.AddWithValue("@SessionId", sessionId);
                oCmd.Parameters.AddWithValue("@NewUtcExpires", utcExpiry);
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
#if TRACE
                Trace.WriteLine(string.Format("SessionOnDemand.Renew({0}).", sessionId));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                Trace.TraceError(string.Format("SessionOnDemand.Renew({0}). Exception: {1}.", sessionId, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
        }

        /// <summary>
        /// Method removes all items associated with this session.
        /// </summary>
        public void Clear(string sessionId)
        {
            SqlCommand oCmd = null;
            try
            {
                oCmd = this.CreateSqlCommand("usp_RemoveSessionItems");
                oCmd.Parameters.AddWithValue("@SessionId", sessionId);
                oCmd.Connection.Open();
                oCmd.ExecuteNonQuery();
#if TRACE
                Trace.WriteLine(string.Format("SessionOnDemand.Clear({0}).", sessionId));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                Trace.TraceError(string.Format("SessionOnDemand.Clear({0}). Exception: {1}.", sessionId, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                this.DisposeSqlCommand(ref oCmd);
            }
        }

        /// <summary>
        /// Method returns the list of items in the collection for this session.
        /// </summary>
        /// <remarks>This method is resource intensive and is designed for use by customer support. 
        /// Do not use from the application code.</remarks>
        /// <param name="includeActualData">Boolean value indicating where or not the actual data should be included or cleared.</param>
        /// <returns>Returns the list of items in the collection for this session.</returns>
        public List<CacheItemInfo> RetrieveCollection(string sessionId, bool includeActualData)
        {
            List<CacheItemInfo> result = new List<CacheItemInfo>();
            SqlCommand oCmd = null;
            SqlDataReader oRdr = null;
            try
            {
                oCmd = this.CreateSqlCommand("usp_RetrieveSessionItems");
                oCmd.Parameters.AddWithValue("@SessionId", sessionId);
                oCmd.Parameters.AddWithValue("@IncludeActualData", includeActualData ? decimal.One : decimal.Zero);
                oCmd.Connection.Open();
                oRdr = oCmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (oRdr.Read())
                {
                    result.Add(new CacheItemInfo()
                    {
                        Id = (string)oRdr["Id"],
                        DataLength = (int)oRdr["DataLength"],
                        Item = (byte[])oRdr["Item"]
                    });
                }
#if TRACE
                Trace.WriteLine(string.Format("SessionOnDemand.RetrieveCollection({0}).", sessionId));
#endif
            }
            catch (Exception ex)
            {
#if TRACE
                Trace.TraceError(string.Format("SessionOnDemand.RetrieveCollection({0}). Exception: {1}.", sessionId, ErrorUtility.BuildFullExceptionMessage(ex)));
#endif
                base.HandleException(ex);
#if DEBUG
                throw;
#endif
            }
            finally
            {
                if (oRdr != null)
                    oRdr.Close();
                this.DisposeSqlCommand(ref oCmd);
            }
            return result;
        }

        public void Dispose()
        {
        }
    }
}
